package com.example;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;
import org.junit.Before;
import org.junit.Test;

import java.io.File;
import java.io.FileInputStream;
import java.net.URL;
import java.net.URLDecoder;
import java.text.SimpleDateFormat;

public class ReadExcelDemo {

    private FileInputStream fis;

    private Workbook workbook;

    private Sheet sheet;

    @Before
    public void init() throws Exception{

        //System.out.println(this.getClass().getName());
        //从类路径获取文件-方式一,获取类对象可以通过Class.forName()或getClass()等，或具体对象获取Class,this.getClass()
        //URL url = getClass().getClassLoader().getResource("测试读取Excel的文件.xls");
        //从类路径获取文件-方式二，只要确保文件在类路径下即target/classes目录下面
        URL url = ReadExcelDemo.class.getClassLoader().getResource("测试读取Excel的文件.xls");
        //url.getFile()获取的文件路径是经过URLencode编码过，需要解码为正确的路径
        String filePath = URLDecoder.decode(url.getFile(), "utf-8");
//        System.out.println(new File(filePath).exists());

        fis = new FileInputStream(filePath);
    }

    @Test
    public void readExcel() throws Exception{
        POIFSFileSystem poifsFileSystem = new POIFSFileSystem(fis);
        workbook = new HSSFWorkbook(poifsFileSystem);//这里直接传入fis好像也是可以的

        sheet = workbook.getSheetAt(0);
        if(null == sheet){
            return;
        }

        int lastRowNum = sheet.getLastRowNum();//返回最后一行的索引

        for (int i = 0; i <=lastRowNum; i++) {
            Row row = sheet.getRow(i);
            if(row == null){
                continue;
            }
            int lastCellNum = row.getLastCellNum();
            for (int j = 0; j <=lastCellNum; j++) {
                Cell cell = row.getCell(j);
                if(null == cell){
                    continue;
                }


                System.out.println(getCellValue(cell));
            }
        }

    }

    public String getCellValue(Cell cell) throws Exception{

        //Cell支持获取的6中数据类型,同时设置也支持这六种类型，同时设置可以是Calendar类型
        /*String value0 = cell.getStringCellValue();
        boolean vlue1 =  cell.getBooleanCellValue();
        Date value2 = cell.getDateCellValue();
        byte value3 = cell.getErrorCellValue();
        double value4 = cell.getNumericCellValue();
        RichTextString value5 = cell.getRichStringCellValue();*/

        //CelType提供的类型
        /*CellType 类型 值
        CELL_TYPE_NUMERIC 数值型 0
        CELL_TYPE_STRING 字符串型 1
        CELL_TYPE_FORMULA 公式型 2
        CELL_TYPE_BLANK 空值 3
        CELL_TYPE_BOOLEAN 布尔型 4
        CELL_TYPE_ERROR 错误 5*/

        //旧版本通过HSSFCell判断类型
        if(cell.getCellType() == CellType.NUMERIC){
//            System.out.println(cell.getCellStyle().getDataFormatString());
//            if(cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("yyyy\\-mm\\-dd\\ hh:mm:ss")){
            if(cell.getCellStyle().getDataFormatString().equals("yyyy\\-mm\\-dd\\ hh:mm:ss")){
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                System.out.println(sdf.format(cell.getDateCellValue()));
            }else{
                return String.valueOf(cell.getNumericCellValue());
            }
        }
        if(cell.getCellType() == CellType.STRING){
            return cell.getRichStringCellValue().toString();
        }
        if(cell.getCellType() == CellType.BOOLEAN){
            return String.valueOf(cell.getBooleanCellValue());
        }
        if(cell.getCellType() == CellType.BLANK){
            return "空值类型";
        }
        if(cell.getCellType() == CellType.FORMULA){
            return "公式类型";
        }
        if(cell.getCellType() == CellType.ERROR){
            return "错误类型";
        }
        return "";
    }

    public void destory() throws Exception{
        if(null != fis){
            fis.close();
        }
    }

}
